<?php  
   include_once($a_fmConnect);
   include_once($a_fmEscape);
   /*
		fonction qui retourne une liste d'utilisateur dont l'utilisateur $id_user a le plus d'affinité
   */
	function sql_liste_user_affinite($id_user)
	{
		/*
		Algo basique tres rapide qui retourne une liste de film par affinité sans tenir compte de la confiance
		$result=mysql_query("SELECT A.FILM_ID,COUNT(A.FILM_ID) AS NB_FILM_COMMUN
							FROM A_REGARDE A
							WHERE A.USER_ID != '".escape($id_user)."'
							AND EXISTS (SELECT A2.FILM_ID 
							            FROM A_REGARDE A2 
							            WHERE A2.USER_ID='".escape($id_user)."'
							            AND A.USER_NOTE_OF_FILM BETWEEN A2.USER_NOTE_OF_FILM-5 
							            AND A2.USER_NOTE_OF_FILM +5)
							AND A.FILM_ID NOT IN (SELECT A3.FILM_ID
										FROM A_REGARDE A3
										WHERE A3.USER_ID='".escape($id_user)."')
							GROUP BY A.FILM_ID
							ORDER BY NB_FILM_COMMUN DESC
							LIMIT 10") or die(mysql_error());*/
		$result=mysql_query("SELECT A.USER_ID,COUNT(A.USER_ID) AS NB_FILM_COMMUN
							FROM A_REGARDE A
							WHERE A.USER_ID != '".escape($id_user)."'
							AND EXISTS (SELECT A2.FILM_ID 
							            FROM A_REGARDE A2 
							            WHERE A2.USER_ID='".escape($id_user)."'
							            AND A.USER_NOTE_OF_FILM BETWEEN A2.USER_NOTE_OF_FILM-5 
							            AND A2.USER_NOTE_OF_FILM +5)
							GROUP BY A.USER_ID
							ORDER BY NB_FILM_COMMUN DESC");
		return $result;
	}
	/*
		fonction qui retourne 10 films par affinité de l'utilisateur
	*/
	function sql_film_affinite($id_user,$nb_film)
	{
		$user_affinite = sql_liste_user_affinite($id_user);
		$nb_film_trouve = 0;
		$film_affinite = array();
		$film_affinite_classe = array();
		while (($row_user_affinite = mysql_fetch_assoc($user_affinite))) {
			$film_affinite[$row_user_affinite['USER_ID']]=sql_not_view_by_1_but_2($id_user,$row_user_affinite['USER_ID']);
			while ($row_film_affinite = mysql_fetch_assoc($film_affinite[$row_user_affinite['USER_ID']])) {
				if(isset($film_affinite_classe[$row_film_affinite['FILM_ID']]))
				{
					$film_affinite_classe[$row_film_affinite['FILM_ID']]+= sql_taux_confiance_between_users($id_user,$row_user_affinite['USER_ID']);
				}
				else
				{
					$film_affinite_classe[$row_film_affinite['FILM_ID']] = sql_taux_confiance_between_users($id_user,$row_user_affinite['USER_ID']);
				}
			}
		}
		arsort($film_affinite_classe);
		return $film_affinite_classe;
	}
	/*
		fonction qui retourne le taux de confiance entre 2 user
	*/
	function sql_taux_confiance_between_users($id_user1,$id_user2)
	{
		$row = mysql_fetch_assoc($result = mysql_query("SELECT TAUX_CONFIANCE FROM A_POUR_AFFINITE WHERE USER_ID1='".escape($id_user1)."' AND USER_ID2='".escape($id_user2)."'"));
		if(mysql_num_rows($result) > 0)
			return $row["TAUX_CONFIANCE"];
		else
			return 100;
	}
	/*
		fonction qui retourne tous les films vu par user2 mais pas par user1
	*/
	function sql_not_view_by_1_but_2($id_user1,$id_user2)
	{
		return mysql_query("SELECT A.FILM_ID
						FROM
						    A_REGARDE A
						    LEFT OUTER JOIN A_REGARDE A2
						      ON A2.FILM_ID = A.FILM_ID
						     AND A2.USER_ID='".escape($id_user1)."'
						WHERE A2.FILM_ID IS NULL
						     AND A.USER_ID='".escape($id_user2)."'");
		
	}
	/*
		FONCTION POUR LA TABLE USER
	*/
		/*
			fonction qui ajoute un utilisateur
		*/
		function sql_add_user($prenom,$nom,$mail,$password)
		{
			mysql_query("INSERT INTO USER(USER_PRENOM,USER_NOM,USER_MAIL,USER_PASS,USER_REGISTERED) VALUES('".escape($prenom)."','".escape($nom)."','".escape($mail)."','".escape($password)."',now())");
			$_SESSION['id'] = mysql_insert_id();
		}
	/*
		FONCTION POUR LA TABLE FILM
	*/
	   	/*
	      ajoute une note a un post
	   	*/
	   	function sql_add_mark_of_film($id_film,$user_id,$mark)
	   	{
	     	if(sql_has_give_mark($id_film,$user_id) && $user_id != 1)
	        	mysql_query("UPDATE A_REGARDE SET USER_NOTE_OF_FILM='".escape($mark)."' WHERE FILM_ID='".escape($id_film)."' AND USER_ID='".escape($user_id)."'");
	      	else
	         	mysql_query("INSERT INTO A_REGARDE(FILM_ID,USER_ID,USER_NOTE_OF_FILM) VALUES('".escape($id_film)."','".escape($user_id)."','".escape($mark)."')");
	   	}
	   	/*
	      	fonction qui retourne true si user a deja donné une note
	   	*/
	   	function sql_has_give_mark($id_post,$user_id)
	   	{
	      	return mysql_num_rows(mysql_query("SELECT * FROM A_REGARDE WHERE FILM_ID='".escape($id_post)."' AND USER_ID='".escape($user_id)."'")) > 0;
	   	}
	   	/*
	   		fonction qui supprime une note d'un utilisateur
	   	*/
	   	function sql_delete_mark_of_film($id_film,$user_id)
	   	{
	     	if(sql_has_give_mark($id_film,$user_id))	   		
	   			mysql_query("DELETE FROM A_REGARDE WHERE FILM_ID='".escape($id_film)."' AND USER_ID='".escape($user_id)."'");
	   	}
?>